"""
批量执行多个SQL脚本，创建存储过程，或者执行多个SQL脚本
根据 db 传参来执行
"""
import sqlparse
from flask import jsonify
from sqlalchemy import text


class ExecuteBatchSQLScripts:
    def __init__(self, db_instance):
        self.db = db_instance
        self.connection = None
        self.cursor = None

    def execute_sql_file(self, file_path):
        """
        执行SQL文件中的多个SQL语句（包括存储过程）
        :param file_path: SQL文件路径。SQL脚本目录在 tzq_flask_all_project/tzq/sql 文件夹
        """
        try:
            # 读取SQL文件内容
            with open(file_path, 'r', encoding='utf-8') as f:
                sql_content = f.read()
            # print('sql_content=', sql_content)
            # 使用sqlparse分割SQL语句（正确处理存储过程）
            statements = sqlparse.split(sql_content)
            # 获取数据库连接
            connection = self.db.engine.connect()
            transaction = connection.begin()  # 开始事务

            try:
                for statement in statements:
                    # 跳过空语句
                    cleaned = statement.strip()
                    if not cleaned:
                        continue

                    # 执行SQL语句
                    connection.execute(text(cleaned))

                transaction.commit()  # 提交事务
                print("SQL文件执行成功")
            except Exception as e:
                transaction.rollback()  # 回滚事务
                raise RuntimeError(f"SQL执行失败: {str(e)}")
            finally:
                connection.close()

        except Exception as e:
            raise RuntimeError(f"文件操作失败: {str(e)}")

    def execute_sql(self, sql_script: str):
        """
        执行sql脚本
        :param sql_script: SQL脚本内容
        :return: 执行是否成功
        """
        # 获取数据库连接
        self.connection = self.db.engine.raw_connection()
        try:
            self.cursor = self.connection.cursor()
            # 使用事务执行脚本
            with self.cursor:
                self.cursor.execute(sql_script)
            self.connection.commit()
            print("脚本执行成功！")
            return True
        except Exception as e:
            self.connection.rollback()
            print(f"执行错误: {e}")
            return False
        finally:
            self.cursor.close()
            self.connection.close()

    def execute_sql_json(self, sql_script: str):
        """
        执行sql脚本
        :param sql_script: SQL脚本内容
        :return: 执行是否成功
        """
        # 获取数据库连接
        self.connection = self.db.engine.raw_connection()
        try:
            self.cursor = self.connection.cursor()
            # 使用事务执行脚本
            with self.cursor:
                self.cursor.execute(sql_script)
            self.connection.commit()
            msg = "脚本执行成功！"
            print("脚本执行成功！")
            # return True
            return jsonify({"code": 1, "msg": msg})
        except Exception as e:
            self.connection.rollback()
            msg = f"执行错误: {e}"
            print(f"执行错误: {e}")
            # return False
            return jsonify({"code": 0, "msg": msg})
        finally:
            self.cursor.close()
            self.connection.close()

    def execute_sql_file_2(self, script_path):
        """
        执行多行脚本
        :param script_path: SQL脚本文件路径
        :return: 执行是否成功
        """
        # 获取数据库连接
        self.connection = self.db.engine.raw_connection()
        try:
            self.cursor = self.connection.cursor()
            # 读取SQL脚本文件内容
            with open(script_path, 'r', encoding='utf-8') as file:
                sql_script = file.read()
            # 使用事务执行脚本
            with self.cursor:
                self.cursor.execute(sql_script)
            self.connection.commit()
            print("脚本执行成功！")
            return True
        except Exception as e:
            self.connection.rollback()
            print(f"执行错误: {e}")
            return False
        finally:
            self.cursor.close()
            self.connection.close()


# 使用示例
if __name__ == "__main__":
    # # 配置数据库连接参数
    # manager = ExecuteBatchSQLScripts(
    #     dbname="log_dev_db",
    #     user="log",
    #     password="1",
    #     host="localhost",
    #     port=5432
    # )
    #
    # base_dir = os.path.dirname(os.path.abspath(__file__))
    # print('base_dir = ', base_dir)
    # template_folder = os.path.join(base_dir, '..', 'sql', 'create_gauss_table_info_view_p.sql')
    #
    # if manager.connect():
    #     # 执行存储过程创建脚本
    #     manager.execute_procedure_script(template_folder)
    #     manager.disconnect()
    pass
